using System;
using System.Data;

using System.Configuration;

using System.Data.OleDb;
using Entity;

namespace SalesVMT
{
    public class CalculationUnitDB
    {
        #region PUBLIC
        public static DataSet GetAll()
        {
            DataSet retVal = null;
            OleDbConnection dbConn = clsConnection.CreateConnection();

            OleDbCommand dbCmd = new OleDbCommand("SELECT * FROM SalesVMT_CalculationUnit", dbConn);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                retVal = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataSet GetList(out int ItemCount, string keyWord, string orderBy, string orderDirection, int currentPage, int pageSize)
        {
            string sql = "";
            string order = "";
            int total = 0;
            total = currentPage * pageSize;
            if (order == "")
                order = "CalculationUnit_Name ASC";
            else
                order = orderBy + " " + orderDirection;
            sql = "Select top " + pageSize.ToString() + " * FROM SalesVMT_CalculationUnit";

            if (total > 0)
                sql = sql + " where CalculationUnit_ID NOT IN (SELECT TOP " + total.ToString() + " CalculationUnit_ID FROM SalesVMT_CalculationUnit Order By " + order + ")";

            sql = sql + " Order By " + order;


            OleDbConnection dbConn = clsConnection.CreateConnection();
            DataSet retVal = null;
            DataSet retVal1 = null;
            OleDbCommand dbCmd;
            OleDbDataAdapter da;

            try
            {

                dbCmd = new OleDbCommand(sql, dbConn);
                dbCmd.CommandType = CommandType.Text;
                retVal = new DataSet();
                da = new OleDbDataAdapter(dbCmd);
                da.Fill(retVal);

                dbCmd = new OleDbCommand("Select count(*) from  SalesVMT_CalculationUnit", dbConn);
                dbCmd.CommandType = CommandType.Text;
                retVal1 = new DataSet();
                da = new OleDbDataAdapter(dbCmd);
                da.Fill(retVal1);

                ItemCount = Convert.ToInt32(retVal1.Tables[0].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;

        }
        public static bool Delete(int _calculationUnit_ID)
        {
            bool retVal = false;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand("DELETE SalesVMT_CalculationUnit WHERE CalculationUnit_ID = @CalculationUnit_ID", dbConn);
            dbCmd.Parameters.AddWithValue("@CalculationUnit_ID", _calculationUnit_ID);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int Insert(CalculationUnitInfo _CalculationUnitInfo)
        {
            int retVal = 0;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            DataSet ds = null;
            DataSet ds1 = null;
            OleDbCommand dbCmd;
            OleDbDataAdapter da;
            try
            {
                string sql = "INSERT INTO SalesVMT_CalculationUnit(CalculationUnit_Name)" +
                " VALUES (@CalculationUnit_Name)";
                dbCmd = new OleDbCommand(sql, dbConn);
                dbCmd.Parameters.AddWithValue("@CalculationUnit_Name", _CalculationUnitInfo.CalculationUnit_Name);
                dbCmd.CommandType = CommandType.Text;
                dbConn.Open();
                dbCmd.ExecuteNonQuery();

                dbCmd = new OleDbCommand("SELECT @@identity", dbConn);
                dbCmd.CommandType = CommandType.Text;
                ds1 = new DataSet();
                da = new OleDbDataAdapter(dbCmd);
                da.Fill(ds1);

                retVal = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Update(CalculationUnitInfo _CalculationUnitInfo)
        {
            bool retVal = false;
            string sql = "UPDATE SalesVMT_CalculationUnit SET CalculationUnit_Name = @CalculationUnit_Name" +
                         " WHERE CalculationUnit_ID=@CalculationUnit_ID";
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand(sql, dbConn);
            dbCmd.Parameters.AddWithValue("@CalculationUnit_Name", _CalculationUnitInfo.CalculationUnit_Name);
            dbCmd.Parameters.AddWithValue("@CalculationUnit_ID", _CalculationUnitInfo.CalculationUnit_ID);
            dbCmd.CommandType = CommandType.Text;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static CalculationUnitInfo GetInfo(int _calculationUnit_ID)
        {
            CalculationUnitInfo retVal = null;
            string sql = "SELECT * FROM SalesVMT_CalculationUnit" +
                        " WHERE CalculationUnit_ID = @CalculationUnit_ID ";
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand(sql, dbConn);
            dbCmd.Parameters.AddWithValue("@CalculationUnit_ID", _calculationUnit_ID);
            dbCmd.CommandType = CommandType.Text;
            OleDbDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new CalculationUnitInfo();
                    retVal.CalculationUnit_ID = Convert.ToInt32(dr["CalculationUnit_ID"]);
                    retVal.CalculationUnit_Name = Convert.ToString(dr["CalculationUnit_Name"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public static int Using(int CalculationUnitID)
        {
            int retVal = 0;
            string sql = "select count(Item_CalculationUnitID) as  CalculationUnit_ID from SalesVMT_Item" +
                        " where Item_CalculationUnitID = @CalculationUnit_ID";
            OleDbConnection dbConn = clsConnection.CreateConnection();
            OleDbCommand dbCmd = new OleDbCommand(sql, dbConn);
            dbCmd.CommandType = CommandType.Text;
            dbCmd.Parameters.AddWithValue("@CalculationUnit_ID", CalculationUnitID);

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Exist(int CalculationUnitID, string CalculationUnitName)
        {
            bool retVal = false;
            OleDbConnection dbConn = clsConnection.CreateConnection();
            string sql = "Select Count(CalculationUnit_Name)  from SalesVMT_CalculationUnit" +
                        " where CalculationUnit_Name = @CalculationUnit_Name and CalculationUnit_ID <> @CalculationUnit_ID";
            OleDbCommand dbCmd = new OleDbCommand(sql, dbConn);
            dbCmd.CommandType = CommandType.Text;
            dbCmd.Parameters.AddWithValue("@CalculationUnit_Name", CalculationUnitName);
            dbCmd.Parameters.AddWithValue("@CalculationUnit_ID", CalculationUnitID);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)

                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        #endregion
    }
}
